package tom.server;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import tom.client.account.Service;
import tom.shared.Bank;
import tom.shared.Account;
import tom.shared.InvItem;
import tom.shared.Invoice;
import tom.shared.Payment;
import tom.shared.TaxCalc;
import tom.shared.Voucher;
import tom.shared.VchItem;

public class AccServiceImpl extends SessionService implements Service {

	private static final long serialVersionUID = 8536451823035479155L;

	private Integer getNextVoucher() {
		ResultSet rs = super.getDatabase().executeQuery(
				"select vouRecieptNr from company where comCode='"
						+ super.getSessVar("comCode") + "' for update");

		Integer lastCode = 1;
		try {
			if (rs.next()) {
				lastCode = rs.getInt(1);
				if (lastCode == null)
					lastCode = 1;
				else
					lastCode++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		getDatabase().executeUpdate(
				"update company set vouRecieptNr=" + lastCode + " where comCode='"
						+ super.getSessVar("comCode") + "'");
		return lastCode;
	}

	@Override
	public Bank selectBank(String id) {
		String sql = "select * from bank where comCode='"
				+ getSessVar("comCode") + "' and bkCode='" + id + "'";
		Bank record = null;
		try {
			ResultSet res = getDatabase().executeQuery(sql);
			while (res.next()) {
				record = new Bank();
				record.bkCode = res.getString("bkCode");
				record.accountNo = res.getString("accountNo");
				record.bkName = res.getString("bkName");
				record.ledgerAcc = res.getString("ledgerAcc");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return record;
	}

	@Override
	public boolean deleteBank(String id) {
		int rowEffect = 0;
		rowEffect = super.getDatabase().executeUpdate(
				"delete from accbook where comCode='" + getSessVar("comCode")
						+ "' and bkCode='" + id + "'");
		log(super.getSessVar("user") + " delete bank " + id);
		return rowEffect > 0;
	}

	@Override
	public boolean insertBank(Bank record) {
		Connection conn = super.getDatabase().getConnection();
		PreparedStatement stmt;
		int rowEffect = 0;
		try {
			stmt = conn.prepareStatement("insert into bank values (?,?,?,?,?)");
			stmt.setString(1, this.getSessVar("comCode"));
			stmt.setString(2, record.bkCode);
			stmt.setString(3, record.accountNo);
			stmt.setString(4, record.bkName);
			stmt.setString(5, record.ledgerAcc);
			rowEffect = stmt.executeUpdate();
		} catch (SQLException e) {
			log(e.getMessage());
		}
		return rowEffect > 0;
	}

	@Override
	public boolean updateBank(Bank record) {
		Connection conn = super.getDatabase().getConnection();
		PreparedStatement stmt;
		int rowEffect = 0;
		try {
			stmt = conn
					.prepareStatement("update bank set accountNo=?, bkName=?, ledgerAcc=? where comCode=? and bkCode=?");
			stmt.setString(1, record.accountNo);
			stmt.setString(2, record.bkName);
			stmt.setString(3, record.ledgerAcc);
			stmt.setString(4, this.getSessVar("comCode"));
			stmt.setString(5, record.bkCode);
			rowEffect = stmt.executeUpdate();
		} catch (SQLException e) {
			log(e.getMessage());
		}
		return rowEffect > 0;
	}

	@Override
	public Account selectAccount(String id) {
		String sql = "select * from account where comCode='"
				+ getSessVar("comCode") + "' and accCode='" + id + "'";
		Account record = null;
		try {
			ResultSet res = getDatabase().executeQuery(sql);
			while (res.next()) {
				record = new Account();
				record.accCode = res.getString("accCode");
				record.descript = res.getString("descript");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return record;
	}

	@Override
	public boolean deleteAccount(String id) {
		int rowEffect = 0;
		rowEffect = super.getDatabase().executeUpdate(
				"delete from account where comCode='" + getSessVar("comCode")
						+ "' and accCode='" + id + "'");
		log(super.getSessVar("user") + " delete account " + id);
		return rowEffect > 0;
	}

	@Override
	public boolean insertAccount(Account record) {
		Connection conn = super.getDatabase().getConnection();
		PreparedStatement stmt;
		int rowEffect = 0;
		try {
			stmt = conn.prepareStatement("insert into account values (?,?,?)");
			stmt.setString(1, this.getSessVar("comCode"));
			stmt.setString(2, record.accCode);
			stmt.setString(3, record.descript);
			rowEffect = stmt.executeUpdate();
		} catch (SQLException e) {
			log(e.getMessage());
		}
		return rowEffect > 0;
	}

	@Override
	public boolean updateAccount(Account record) {
		Connection conn = super.getDatabase().getConnection();
		PreparedStatement stmt;
		int rowEffect = 0;
		try {
			stmt = conn
					.prepareStatement("update account set descript=? where comCode=? and accCode=?");
			stmt.setString(1, record.descript);
			stmt.setString(2, this.getSessVar("comCode"));
			stmt.setString(3, record.accCode);
			rowEffect = stmt.executeUpdate();
		} catch (SQLException e) {
			log(e.getMessage());
		}
		return rowEffect > 0;
	}

	@Override
	public List<Bank> selectBank() {
		List<Bank> list = new ArrayList<Bank>();
		ResultSet rs = getDatabase().executeQuery(
				"select * from bank where comCode='" + getSessVar("comCode")
						+ "'");
		Bank record = null;
		try {
			while (rs.next()) {
				record = new Bank();
				record.bkCode = rs.getString("bkCode");
				record.accountNo = rs.getString("accountNo");
				record.bkName = rs.getString("bkName");
				record.ledgerAcc = rs.getString("ledgerAcc");
				list.add(record);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public boolean insertVoucher(Voucher record) {
		// record.comCode = this.getSessVar("comCode");
		record.creUser = this.getSessVar("user");
		Connection conn = super.getDatabase().getConnection();
		PreparedStatement stmt;
		int rowEffect = 0;
		try {
			stmt = conn
					.prepareStatement("insert into voucher (comCode,voucherNo,systemKind,docuKind,docRef,"
							+ "vchDate,contCode,contAddr,totalAmt,totalAmtText,totalItem,totalVat,"
							+ "spcDeduct,taxRate,totalTax,amtPaid,creUser,creTime)"
							+ " values (?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,now())");

			record.voucherNo = getNextVoucher();

			record.docRef = formatVoucherDocRef(record.voucherNo);

			stmt.setString(1, this.getSessVar("comCode"));
			stmt.setInt(2, record.voucherNo);
			stmt.setString(3, record.systemKind);
			stmt.setString(4, record.docuKind);
			stmt.setString(5, record.docRef);
			stmt.setObject(6, record.vchDate);
			stmt.setString(7, record.contCode);
			stmt.setString(8, record.contAddr);
			stmt.setDouble(9, record.totalAmt);
			stmt.setString(10, record.totalAmtText);
			stmt.setDouble(11, record.totalItem);
			stmt.setDouble(12, record.totalVat);
			stmt.setDouble(13, record.spcDeduct);
			stmt.setDouble(14, record.taxRate);
			stmt.setDouble(15, record.totalTax);
			stmt.setDouble(16, record.amtPaid);
			stmt.setString(17, record.creUser);
			rowEffect = stmt.executeUpdate();

			stmt = conn
					.prepareStatement("insert into vchitem (comCode,voucherNo,invNo,docRef,paidAmt,limitPaid,remain) values (?,?,?,?,?,?,?)");
			PreparedStatement ist = conn
					.prepareStatement("update invoice set pendingReceipt=? where "
							+ "comCode=? and invNo=?");

			for (VchItem item : record.items) {
				stmt.setString(1, this.getSessVar("comCode"));
				stmt.setInt(2, record.voucherNo);
				stmt.setInt(3, item.invNo);
				stmt.setString(4, item.docRef);
				stmt.setDouble(5, item.paidAmt);
				stmt.setDouble(6, item.limitPaid);
				stmt.setDouble(7, item.remain);

				rowEffect = stmt.executeUpdate();

				ist.setDouble(1, item.paidAmt);
				ist.setString(2, this.getSessVar("comCode"));
				ist.setInt(3, item.invNo);
				rowEffect = ist.executeUpdate();
			}
			
			
			insertPayment(record);
		} catch (SQLException e) {
			log(e.getMessage());
		}
		return rowEffect > 0;
	}

	private Voucher createVchRecord(ResultSet res) throws SQLException {
		Voucher record = new Voucher();
		record.voucherNo = res.getInt("voucherNo");
		record.vchDate = res.getDate("vchDate");
		record.systemKind = res.getString("systemKind");
		record.docuKind = res.getString("docuKind");
		record.docRef = res.getString("docRef");
		record.totalAmt = res.getDouble("totalAmt");
		record.totalAmtText = res.getString("totalAmtText");
		record.amtPaid = res.getDouble("amtPaid");
		record.totalItem = res.getDouble("totalItem");
		record.totalTax = res.getDouble("totalTax");
		record.totalVat = res.getDouble("totalVat");
		record.contCode = res.getString("contCode");
		record.taxRate = res.getDouble("taxRate");
		record.status = res.getString("status");
		record.creUser = res.getString("creUser");
		record.creTime = res.getTimestamp("creTime");
		return record;
	}

	private String formatVoucherDocRef(Integer invNo) {
		return String.format("%03d-%04d", invNo / 10000, invNo % 10000);
	}

	@Override
	public Voucher selectVoucher(Integer voucherNo) {
		String sql = "select * from voucher  where comCode='"
				+ super.getSessVar("comCode") + "' and voucherNo=" + voucherNo;
		ResultSet res = getDatabase().executeQuery(sql);
		Voucher record = null;
		try {
			if (res.next()) {
				record = createVchRecord(res);

				sql = "select * from vchitem where comCode='"
						+ getSessVar("comCode") + "' and voucherNo="
						+ voucherNo;
				res = getDatabase().executeQuery(sql);

				while (res.next()) {
					VchItem item = new VchItem();
					item.invNo = res.getInt("invNo");
					item.voucherNo = res.getInt("voucherNo");
					item.docRef = res.getString("docRef");
					item.paidAmt = res.getDouble("paidAmt");
					item.limitPaid = res.getDouble("limitPaid");
					item.remain = res.getDouble("remain");
					record.items.add(item);
				}

				sql = "select payment.*, paymeth.paymentMethod from payment, paymeth where payment.comCode='"
						+ getSessVar("comCode")
						+ "' and payment.comCode=paymeth.comCode and voucherNo="
						+ voucherNo
						+ " and payment.payMethod=paymeth.paymethCode";
				res = getDatabase().executeQuery(sql);
				while (res.next()) {
					Payment pay = new Payment();
					pay.voucherNo = voucherNo;
					pay.payNo = res.getInt("payNo");
					pay.systemKind = res.getString("systemKind");
					pay.itemKind = res.getString("itemKind");
					pay.docNo = res.getString("docNo");
					pay.payDate = res.getDate("payDate");
					pay.chkCardNo = res.getString("chkCardNo");
					pay.ledgerAcc = res.getString("ledgerAcc");
					pay.checkDate = res.getDate("chkCardDate");
					pay.amt = res.getDouble("amt");
					pay.payMethod = res.getInt("payMethod");
					pay.payMethodName = res.getString("paymentMethod");
					record.payments.add(pay);
				}
			}
		} catch (SQLException e) {
			log(e.getMessage());
			e.printStackTrace(System.out);
		}
		return record;
	}

	/* (non-Javadoc)
	 * @see tom.client.account.Service#insertPayment(tom.shared.Voucher)
	 */
	@Override
	public boolean insertPayment(Voucher voucher) {
		int rowEffect = 0;
		Database db = getDatabase();
		Object[] params = null;
		boolean overLimit = false;
		try {
			db.executeUpdate("start transaction");
			if (voucher.payments.size() > 0) {
				ResultSet rs = null;
				for (Payment record : voucher.payments) {
					if (record.payMethod != 0 && record.payMethod != 2) {
					}
					rs = db.executeQuery("select paymentNo from company where comCode='"
							+ getSessVar("comCode") + "'");
					if (rs.next()) {
						record.payNo = rs.getInt(1);
						if (record.payNo == null)
							record.payNo = 1;
						else
							record.payNo++;
					}

					db.executeUpdate("update company set paymentNo="
							+ record.payNo + " where comCode='"
							+ getSessVar("comCode") + "'");

					record.itemKind = "P";
					if (record.payMethod == 0)
						record.itemKind = "D";

					if (!record.itemKind.equals("P")) {

						rs = db.executeQuery("SELECT ledgerAcc FROM windmill.itemkind where comCode = '"
								+ getSessVar("comCode")
								+ "' and systemKind = '"
								+ record.systemKind
								+ "' and itemkCode = '" + record.itemKind + "'");
					} else {
						rs = db.executeQuery("SELECT ledgerAcc FROM windmill.paymeth where comCode = '"
								+ getSessVar("comCode")
								+ "' and paymethCode = " + record.payMethod);
					}

					if (rs.next()) {
						record.ledgerAcc = rs.getString(1);
					}

					if (record.payMethod == 2)
						record.clearAmt = 0.0;
					else
						record.clearAmt = record.amt;
					record.voucherNo = voucher.voucherNo;
					record.systemKind = voucher.systemKind;
					db.createSQL(
							"insPayment",
							"insert into payment values (?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,NOW(), ?,NOW(),?)");
					params = new Object[26];
					params[0] = this.getSessVar("comCode");
					params[1] = record.payNo;
					params[2] = record.systemKind;
					params[3] = record.itemKind;
					params[4] = record.invNo;
					params[5] = record.docNo;
					params[6] = record.payDate;
					params[7] = record.contCode;
					params[8] = record.payRef;
					params[9] = record.payMethod;
					params[10] = record.amt;
					params[11] = record.clearAmt;
					params[12] = record.ledgerAcc;
					params[13] = record.voucherNo;
					params[14] = record.bkCode;
					params[15] = record.accountNo;
					params[16] = record.chkCardNo;
					params[17] = record.bankCardName;
					params[18] = record.checkDate;
					params[19] = record.cardExpDate;
					params[20] = record.cardOwner;
					params[21] = record.bookingRun;
					params[22] = record.clearingDate;
					params[23] = getSessVar("user");
					params[24] = getSessVar("user");
					params[25] = record.note;
					rowEffect = db.execSqlMap("insPayment", params);
				}
				// bal = voucher.totalAmt - payAmt;
				// if (bal != 0) {
				// Payment p = new Payment();
				// p.systemKind = inv.systemKind;
				// p.invNo = inv.invNo;
				// p.amt = bal;
				// p.payMethod = 0;
				// p.payDate = inv.invDate;
				// inv.payments.add(p);
				// }
				
				
/*				if (bal == 0) {
					db.createSQL("updatePaid",
							"update voucher set totalPaid = ? where invNo = ? and comCode='"
									+ getSessVar("comCode") + "'");

					// เปลี่ยน DocuKind เมื่อ เป็นบิลเงินเชื่อ record.amt =
					// 0
					params = new Object[3];
					inv.docuKind = "I";
					if (inv.contCode.equals("999999")) { // บิลเงินสดกำกับภาษีอย่างย่อ
						inv.docuKind = "H";
					}
					if (inv.totalPaid < inv.totalAmt) { // บิลเงินเชื่อ
						inv.docuKind = "C";
					}
					params[1] = inv.totalPaid;
					params[2] = inv.invNo;
					rowEffect = db.execSqlMap("updatePaid", params);
					db.executeUpdate("update contact set custBalance=custBalance+"
							+ (inv.totalAmt - paid)
							+ " where comCode='"
							+ getSessVar("comCode")
							+ "' and contCode='"
							+ inv.contCode + "'");
					// inv.status = "1";
					this.updateStock(inv);
				}*/

			}
			db.executeUpdate("commit");
		} catch (SQLException e) {
			log(e.getMessage());
			getDatabase().executeUpdate("rollback");
		}

		return rowEffect > 0 && !overLimit;
	}

	@Override
	public List<TaxCalc> selectTaxCalc() {
		List<TaxCalc> list = new ArrayList<TaxCalc>();
		Database db = getDatabase();
		ResultSet rs = db.executeQuery("select * from taxcalc");
		try {
			while (rs.next()) {
				list.add(new TaxCalc(rs.getDouble("percent"), rs.getString("descript")));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}	
	
}
